package com.xquant.databasemanagement.service;

import com.xquant.databasemanagement.bean.CommonObject;
import com.xquant.databasemanagement.bean.DatabaseBean;
import com.xquant.databasemanagement.bean.SequenceBean;
import com.xquant.databasemanagement.bean.table.TableBean;
import com.xquant.databasemanagement.config.ConstantConfig;
import freemarker.template.Configuration;
import freemarker.template.Template;
import freemarker.template.TemplateException;

import java.io.*;
import java.net.URL;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Objects;

public class ExportOracleSQLService {
    Configuration configuration;
    Writer out = null;
    File file = null;





    private ExportOracleSQLService(){

        URL url =  this.getClass().getResource("/sqlmodel/oracle");
        configuration = new Configuration();
        try {
            configuration.setDirectoryForTemplateLoading(new File("src\\main\\resources\\sqlmodel\\oracle"));
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 导出序列脚本
     * @param sequenceBeanList
     */
    private void exportSequenceOracleSql(List<SequenceBean> sequenceBeanList,String operationType) throws IOException, TemplateException {
        if (sequenceBeanList==null||sequenceBeanList.size()<=0)
            return;
        try {
            Map<String, Object> dataMap = new HashMap<String, Object>();
            dataMap.put("sequenceList", sequenceBeanList);
            Template template;
            if (operationType.equals(ConstantConfig.UPDATE)){
                template = configuration.getTemplate("AlterSequencesOracle.ftl");
            }else
                template = configuration.getTemplate("SequencesOracle.ftl");
            File docFile = new File(file.toString() + "\\" +operationType+ "SequencesOracle.sql");
            out = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(docFile)));
            StringWriter stringWriter = new StringWriter();
            template.process(dataMap, stringWriter);
            out.write(Objects.requireNonNull(formatSql(stringWriter.toString().toUpperCase())));
        }finally {
            if (out!=null){
                out.flush();
                out.close();
                out = null;
            }
        }
    }


    /**
     * 导出表脚本
     * @param tableBeanList
     */
    private void exportTableOracleSql(List<TableBean> tableBeanList,String operationType) throws IOException, TemplateException {
        if (tableBeanList==null||tableBeanList.size()<=0)
            return;
        try {
            CompareService.getCompareService().classifyKey(tableBeanList);
            Map<String, Object> dataMap = new HashMap<String, Object>();
            dataMap.put("tableList", tableBeanList);
            Template template = configuration.getTemplate("TableOracle.ftl");
            File docFile = new File(file.toString() + "\\" + operationType + "TableOracle.sql");
            out = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(docFile)));
            StringWriter stringWriter = new StringWriter();
            template.process(dataMap, stringWriter);
            out.write(Objects.requireNonNull(formatSql(stringWriter.toString().toUpperCase())));
        }finally {
            if (out!=null){
                out.flush();
                out.close();
                out = null;
            }
        }

    }


    /**
     * 添加列
     * @param tableBeanList
     */
    private void exportAlterTableOracleSql(List<TableBean> tableBeanList,String operationType) throws IOException, TemplateException {
        if (tableBeanList==null||tableBeanList.size()<=0)
            return;
        try {
            Map<String, Object> dataMap = new HashMap<String, Object>();
            dataMap.put("tableList", tableBeanList);
            Template template = configuration.getTemplate("AlterTableColumnOracle.ftl");
            File docFile = new File(file.toString() + "\\" + operationType + "AlterTableOracle.sql");
            out = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(docFile)));
            StringWriter stringWriter = new StringWriter();
            template.process(dataMap, stringWriter);
            out.write(Objects.requireNonNull(formatSql(stringWriter.toString().toUpperCase())));
            for (TableBean t : tableBeanList) {
                t.uniqueKeys = null;
                t.primaryKeys = null;
            }
        }finally {
            if (out!=null){
                out.flush();
                out.close();
                out = null;
            }
        }

    }

    /**
     * 导出索引
     * @param tableBeanList
     * @throws IOException
     * @throws TemplateException
     */
    private void exportIndexOracleSql(List<TableBean> tableBeanList,String operationType) throws IOException, TemplateException {
        if (tableBeanList==null||tableBeanList.size()<=0)
            return;
        try {
            Map<String, Object> dataMap = new HashMap<String, Object>();
            CompareService.getCompareService().classifyIndex(tableBeanList);
            dataMap.put("tableList", tableBeanList);
            Template template = configuration.getTemplate("TableIndexOracle.ftl");
            File docFile = new File(file.toString() + "\\"+ operationType + "IndexOracle.sql");
            out = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(docFile)));
            StringWriter stringWriter = new StringWriter();
            template.process(dataMap, stringWriter);
            out.write(Objects.requireNonNull(formatSql(stringWriter.toString().toUpperCase())));
            for (TableBean t : tableBeanList) {
                t.groupIndexes = null;
                t.groupUniqueIndexes = null;
            }
        }finally {
            if (out!=null){
                out.flush();
                out.close();
                out = null;
            }
        }
    }

    /**
     * 导出视图、触发器、函数、储存过程及初始化数据脚本
     * @param commonObjectList
     * @param commonType
     */
    private void exportCommonObjectOracleSql(List<CommonObject> commonObjectList,String commonType,String operationType) throws IOException, TemplateException {
        if (commonObjectList==null||commonObjectList.size()<=0)
            return;
        try {
            Map<String, Object> dataMap = new HashMap<String, Object>();
            dataMap.put(commonType, commonObjectList);
            Template template = configuration.getTemplate(commonType + ".ftl");
            File docFile = new File(file.toString() + "\\" + operationType + commonType + ".sql");
            out = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(docFile)));
            StringWriter stringWriter = new StringWriter();
            template.process(dataMap, stringWriter);
            out.write(Objects.requireNonNull(formatSql(stringWriter.toString().toUpperCase())));
        }finally {
            if (out!=null){
                out.flush();
                out.close();
                out = null;
            }
        }
    }





    public void exportCompleteOracle(DatabaseBean databaseBean, File file,String type) throws IOException, TemplateException {
        this.file = file;
        exportCommonObjectOracleSql(databaseBean.getBasicDataList(), ConstantConfig.ORACLE_BASICDATA_FTL,type);
        exportSequenceOracleSql(databaseBean.getSequenceList(),type);
        exportTableOracleSql(databaseBean.getTableList(),type);
        exportIndexOracleSql(databaseBean.getTableList(),type);
        exportCommonObjectOracleSql(databaseBean.getViewList(), ConstantConfig.ORACLE_VIEW_FTL,type);
        exportCommonObjectOracleSql(databaseBean.getFunctionList(), ConstantConfig.ORACLE_FUNCTION_FTL,type);
        exportCommonObjectOracleSql(databaseBean.getProcedureList(), ConstantConfig.ORACLE_PROCEDURE_FTL,type);
        exportCommonObjectOracleSql(databaseBean.getTriggerList(), ConstantConfig.ORACLE_TRIGGER_FTL,type);
    }

    public void exportAddOracle(DatabaseBean databaseBean, File file,String type) throws IOException, TemplateException {
        this.file = file;
        exportAlterTableOracleSql(databaseBean.getTableList(),type);
        exportIndexOracleSql(databaseBean.getTableList(),type);
        exportSequenceOracleSql(databaseBean.getSequenceList(),type);
        exportCommonObjectOracleSql(databaseBean.getViewList(), ConstantConfig.ORACLE_VIEW_FTL,type);
        exportCommonObjectOracleSql(databaseBean.getFunctionList(), ConstantConfig.ORACLE_FUNCTION_FTL,type);
        exportCommonObjectOracleSql(databaseBean.getProcedureList(), ConstantConfig.ORACLE_PROCEDURE_FTL,type);
        exportCommonObjectOracleSql(databaseBean.getTriggerList(), ConstantConfig.ORACLE_TRIGGER_FTL,type);
    }




    private boolean verifySql(String sql){
        com.alibaba.druid.sql.dialect.oracle.parser.OracleStatementParser
                parser=new com.alibaba.druid.sql.dialect.oracle.parser.OracleStatementParser(sql);
        boolean flag=true;
        try{
            parser.parseStatementList();
        }catch(Exception e){
            flag=false;
        }
        return flag;
    }

    private String formatSql(String sql){
        //先校验sql
        String formatSql = sql;
        if (verifySql(sql)) {
            //格式化
            formatSql = com.alibaba.druid.sql.SQLUtils.formatOracle(sql);
        }
        //判断是否已经格式化过
        if(!sql.equals(formatSql)){
            return formatSql;
        }
        return null;
    }

    private static ExportOracleSQLService exportOracleSQLService = new ExportOracleSQLService();

    public static ExportOracleSQLService getExportOracleSQLService(){
        return exportOracleSQLService;
    }

}
